<?php

function get_orgId_by_sponsor($sponsor_uid) {
    $query = "SELECT * FROM {content_type_organization} WHERE field_org_sponsor_uid = %d";
    $result = db_fetch_object(db_query($query, $sponsor_uid));
    return $result->nid;
}

function get_orgId_by_seller($seller_uid) {
    $content = content_profile_load('profile', $seller_uid);
    return $content->field_profile_org[0]['nid'];
}

function get_org_nid_list_by_agent($agent_uid) {
    $query = "SELECT * FROM {content_type_organization} WHERE field_org_agent_uid = %d";
    $list = array();
    $result = db_query($query, $agent_uid);
    while ($s = db_fetch_object($result)) {
        $list[] = $s->nid;
    }
    return $list;
}

function get_org_list_by_agent($agent_uid) {
    $query = "SELECT * FROM {content_type_organization} WHERE field_org_agent_uid = %d";
    $list = array();
    $result = db_query($query, $agent_uid);
    while ($s = db_fetch_object($result)) {
        $list[] = $s;
    }
    return $list;
}

function get_sales_campaign_list_by_org($org_nid) {
    $query = "SELECT * FROM {content_type_sale_campaign} WHERE field_campaign_org_nid = %d";
    $list = array();
    $result = db_query($query, $org_nid);
    while ($s = db_fetch_object($result)) {
        $list[] = $s;
    }
    return $list;
}

function get_sales_campaign_nid_list_by_org($org_nid) {
    $query = "SELECT * FROM {content_type_sale_campaign} WHERE field_campaign_org_nid = %d";
    $list = array();
    $result = db_query($query, $org_nid);
    while ($s = db_fetch_object($result)) {
        $list[] = $s->nid;
    }
    return $list;
}

/*
 * get campaign nid by seller uid, only get current valid campaign
 * 
 * @param $seller_uid
 *      seller's user id
 * 
 * @return
 *      campaign's nid
 */

function get_sales_compaign_nid_by_seller($seller_uid) {
//    $campaigns = db_query("SELECT * FROM {content_type_sale_campaign} WHERE content_type_sale_campaign.field_campaign_org_nid IN 
//        (SELECT content_type_profile.field_profile_org_nid FROM {content_type_profile} WHERE content_type_profile.nid IN 
//        (SELECT node.nid FROM {node} WHERE node.uid = %d))", $seller_uid);
    
    $campaigns = db_query("SELECT * FROM {content_type_sale_campaign} WHERE content_type_sale_campaign.field_campaign_org_nid IN 
        (SELECT content_type_profile.field_profile_org_nid FROM {content_type_profile} WHERE content_type_profile.nid IN 
        (SELECT node.nid FROM {node} WHERE node.uid = %d))", $seller_uid);
    
    
    $campaign_nid;
    while ($c = db_fetch_object($campaigns)) {
        $from = explode("T", $c->field_duration_value);
        $from_date = $from[0];
        $to = explode("T", $c->field_duration_value2);
        $to_date = $to[0];
        $from_year = ((int) substr($from_date, 0, 4));
        $from_month = ((int) substr($from_date, 5, 2));
        $from_day = ((int) substr($from_date, 8, 2));
        $from_timestamp = mktime(0, 0, 0, $from_month, $from_day, $from_year);

        $to_year = ((int) substr($to_date, 0, 4));
        $to_month = ((int) substr($to_date, 5, 2));
        $to_day = ((int) substr($to_date, 8, 2));
        $to_timestamp = mktime(0, 0, 0, $to_month, $to_day, $to_year);
        if (time() >= $from_timestamp && time() <= $to_timestamp) {
            $campaign_nid = $c->nid;
        }
    }
    return $campaign_nid;
}

/*
 * get list of campaign nids by sponsor uid
 * 
 * @param $seller_uid
 *      seller user id
 * 
 * @return
 *      An array of node ids mapping to the campaign title
 */
function get_sales_compaign_nid_by_sponsor($seller_uid) {
    if ($seller_uid == 1) {
        $result = db_query("SELECT node.nid, node.title FROM {node} WHERE node.nid IN
            (SELECT content_type_sale_campaign.nid FROM {content_type_sale_campaign})");
    } else {
        //sponsor uid -> campaign
        $result = db_query("SELECT node.nid, node.title FROM {node} WHERE node.nid IN 
            (SELECT content_type_sale_campaign.nid FROM {content_type_sale_campaign} WHERE field_campaign_org_nid IN 
            (SELECT content_type_organization.nid FROM {content_type_organization} WHERE content_type_organization.field_org_sponsor_uid = %d))", $seller_uid);
    }
    $object = array();
    while ($o = db_fetch_object($result)) {
        $object[$o->nid] = $o->title;
    }
    return $object;
}


function get_agent_uid_by_org_nid($nid){
    $sql = 'SELECT field_org_agent_uid FROM {content_type_organization} where nid = %d';
    $result = db_query($sql, array($nid));
    
    return db_fetch_object($result)->field_org_agent_uid;
}

/**
 * find all the sellers of one certain sponsor.
 * @param type $sponsor_uid
 * @return sellers array. 
 */
function get_sellers_by_sponsor($sponsor_uid){
    $org_nid = get_orgId_by_sponsor($sponsor_uid);
    
    $query = "SELECT u.uid as uid, u.name as name, ctp.field_profile_org_nid as org_nid FROM {content_type_profile} ctp JOIN {node} n on ctp.nid=n.nid JOIN {users} u ON n.uid = u.uid  WHERE ctp.field_profile_org_nid = %d";
    $list = array();
    $result = db_query($query, $org_nid);
    while ($s = db_fetch_object($result)) {
        $list[] = $s;
    }
    return $list;
}

?>
